package com.mjgy.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.mjgy.repository.UserReportRepository;
import com.mjgy.service.UserReportService;

/**
* @Description: TODO
* @author quanlq
* @date 2018年11月6日
* @version V1.0
*/
@Service("userReportService")
public class UserReportServiceImpl implements UserReportService{
	
	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private UserReportRepository userReportRepository;

	@Override
	public List<Map<String, Object>> findListBySex(Integer sex, String city, Integer identity, String nickname, Integer page, Integer size) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("rpt.id");
		sql.append(",rpt.passive_id");
		sql.append(",usr.province");
		sql.append(",usr.city");
		sql.append(",usr.nickname");
		sql.append(",rpt.active_id");
		sql.append(",(SELECT nickname FROM e_user WHERE id = rpt.active_id LIMIT 1) reporterName");
		sql.append(",usr.identity");
		sql.append(",rpt.content");
		sql.append(",rpt.images");
		sql.append(",rpt.create_time");
		sql.append(",(SELECT IFNULL(SUM(amount), 0) FROM pay_log WHERE payer_id = rpt.passive_id) consumption");
		sql.append(",(SELECT IFNULL(SUM(amount), 0) FROM pay_log WHERE collecter_id = rpt.passive_id) income");
		sql.append(" FROM e_user_report rpt");
		sql.append(" JOIN e_user usr ON rpt.passive_id = usr.id");
		sql.append(" WHERE 1 = 1");
		if(sex != null){
			sql.append(" AND usr.sex = :sex");
		}
		if(!StringUtils.isEmpty(city)){
			sql.append(" AND usr.city LIKE CONCAT('%', CONCAT(:city, '%'))");
		}
		if(identity != null){
			sql.append(" AND usr.identity = :identity");
		}
		if(!StringUtils.isEmpty(nickname)){
			sql.append(" AND usr.nickname LIKE CONCAT('%', CONCAT(:nickname, '%'))");
		}
		sql.append(" ORDER BY rpt.create_time DESC");
		sql.append(" LIMIT :pageIndex, :size");
		
		Query query = entityManager.createNativeQuery(sql.toString());
		if(sex != null){
			query.setParameter("sex", sex);
		}
		if(!StringUtils.isEmpty(city)){
			query.setParameter("city", city);
		}
		if(identity != null){
			query.setParameter("identity", identity);
		}
		if(!StringUtils.isEmpty(nickname)){
			query.setParameter("nickname", nickname);
		}
		query.setParameter("pageIndex", (page-1)*size);
		query.setParameter("size", size);
		List resultList = query.getResultList();
		
		List<Map<String, Object>> list = new ArrayList<>();
		for(Object row : resultList){
			Object[] cells = (Object[]) row;
			Map<String, Object> map = new HashMap<>();
			map.put("id", cells[0]);
			map.put("userId", cells[1]);
			map.put("province", cells[2]);
			map.put("city", cells[3]);
			map.put("nickname", cells[4]);
			map.put("reporterId", cells[5]);
			map.put("reporterName", cells[6]);
			map.put("identity", cells[7]);
			map.put("content", cells[8]);
			map.put("images", cells[9]);
			map.put("createTime", cells[10]);
			map.put("consumption", cells[11]);
			map.put("income", cells[12]);
			list.add(map);
		}
		
		return list;
	}

	@Override
	public Long countBySex(Integer sex, String city, Integer identity, String nickname) {
		return userReportRepository.countBySex(sex, city, identity, nickname);
	}

}
